﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindowsFormsApplication1.Common;
using FanjiaHotelPlatform.utils.Kits;
using log4net.Repository.Hierarchy;
using Maticsoft.DBUtility;

namespace WindowsFormsApplication1
{
    public partial class HotelMerge : Form
    {
        public HotelMerge()
        {
            InitializeComponent();
        }

        private void HotelMerge_Load(object sender, EventArgs e)
        {
            var supplierOrderList = new List<SupplierInfo>()
            {
                new SupplierInfo("", "==请选择=="),
                new SupplierInfo("607B69C3-2DBA-41B5-BCAF-10A014FAF1DD", "畅联-亚朵"),
                new SupplierInfo("073A26B3-874C-4903-B787-6465EE23789B", "如家"),
                new SupplierInfo("0C1C1CDC-C831-4ECF-B362-A47F0E1AC529", "锦江-铂涛直连"),
                new SupplierInfo("D1D0C7F2-DC98-4B64-B36B-F6CD9E7FBB08", "畅联-格林"),
                //new SupplierInfo("CE833C6E-D06F-46A3-B53E-204E91E83AB4", "畅联-君亭"),
                new SupplierInfo("557C7AB5-5816-4C1F-8AED-339A96177143", "东呈直连"),
                new SupplierInfo("89E38E71-47D3-4946-92F4-B41E55ECD38F", "华住直连"),
                //new SupplierInfo("118F9754-0B86-47CB-AAC2-FC75220D90A6", "畅联-都市118"),

                //new SupplierInfo("A7685C06-629D-454B-98DA-81F4E6DE1433", "携程直连"),
                //new SupplierInfo("B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC", "美团直连"),
                //new SupplierInfo("798BD348-9911-4DB4-92CA-848BB0064EC0", "千淘科技"),
                //new SupplierInfo("61FDCC1D-888B-4A53-98ED-40AC18A156CB", "途牛笛风"),
                //new SupplierInfo("24924AF6-48D4-4B7A-BA81-103DA6084C69", "四海度假"),
                //new SupplierInfo("A8375567-B63C-1B9D-F2BC-AA96D2605F53", "丽呈直连"),
                //new SupplierInfo("138E28A8-17AC-47BD-AD2F-4C032CC1F994", "神兽国旅"),
                //new SupplierInfo("00000000-0000-0000-0000-000000000000", "自营")
            };

            this.comboBox1.DataSource = supplierOrderList;
            this.comboBox1.DisplayMember = "SupplierName";
            this.comboBox1.ValueMember = "SupplierId";

            var supplierTorList = new List<SupplierInfo>()
            {
                new SupplierInfo("", "==请选择=="),
                new SupplierInfo("A7685C06-629D-454B-98DA-81F4E6DE1433", "携程直连"),
                new SupplierInfo("B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC", "美团直连"),
                new SupplierInfo("798BD348-9911-4DB4-92CA-848BB0064EC0", "千淘科技"),
                new SupplierInfo("00000000-0000-0000-0000-000000000000", "自营")
            };

            this.comboBox2.DataSource = supplierTorList;
            this.comboBox2.DisplayMember = "SupplierName";
            this.comboBox2.ValueMember = "SupplierId";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(this.comboBox1.SelectedValue.ToString()))
            {
                MessageBox.Show("请选择直连酒店供应商！");
                return;
            }
            if (string.IsNullOrEmpty(this.comboBox2.SelectedValue.ToString()))
            {
                MessageBox.Show("请选择对应映射供应商！");
                return;
            }

            SupplierHotelMergeDeal();
        }

        private void SupplierHotelMergeDeal()
        {
            //直连酒店关联
            var batId = $"{DateTime.Now.ToString("yyyy-MM-dd")}";
            var supplierId = Guid.Parse(this.comboBox1.SelectedValue.ToString());
            var supplierName = this.comboBox1.Text;

            var toSupplierId = Guid.Parse(this.comboBox2.SelectedValue.ToString());

            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeSupplierGroup");

            //关联关系数据
            var mappingDt = DbHelperSQL.Query("SELECT * FROM [HotelMeger_SupplierMapping]").Tables[0];

            //直连供应商已存在映射
            var supplierMap = DbHelperSQL
                .Query(
                    $@"SELECT m.HotelId,m.SupplierHotelId,b.Name HotelName FROM [HotelMappings] m,[HotelBasicInfoes] b WHERE m.HotelId=b.Id AND m.SupplierId = '{supplierId}' AND b.IsDeleted=0 AND m.IsDeleted=0 AND SupplierHotelId IN(SELECT [SupplierHId] FROM [HotelMeger_SupplierMapping])")
                .Tables[0];

            //目标供应商已存在映射
            var toMap = DbHelperSQL.Query(
                $@"SELECT m.HotelId,m.SupplierHotelId,b.Name HotelName FROM [HotelMappings] m,[HotelBasicInfoes] b WHERE m.HotelId=b.Id AND m.SupplierId='{toSupplierId}' AND b.IsDeleted=0 AND m.IsDeleted=0 AND SupplierHotelId IN(SELECT [ToHotelId] FROM [HotelMeger_SupplierMapping])").Tables[0];

            var rstDt = new DataTable();
            rstDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
            rstDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
            rstDt.Columns.Add(new DataColumn("HotelName", typeof(string)));
            rstDt.Columns.Add(new DataColumn("SupplierId", typeof(Guid)));
            rstDt.Columns.Add(new DataColumn("BatId", typeof(string)));

            var oneMap = new DataTable();
            oneMap.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
            oneMap.Columns.Add(new DataColumn("HotelName", typeof(string)));
            oneMap.Columns.Add(new DataColumn("SupplierId", typeof(Guid)));
            oneMap.Columns.Add(new DataColumn("SupplierHotelId", typeof(string)));
            oneMap.Columns.Add(new DataColumn("BatId", typeof(string)));

            foreach (DataRow mapDr in mappingDt.Rows)
            {
                try
                {
                    //目标供应商的映射
                    var toHotelId = mapDr["ToHotelId"].ToString();
                    var tMap = toMap.Select($"SupplierHotelId='{toHotelId}'");

                    //直连供应商的映射
                    var supplierHId = mapDr["SupplierHId"].ToString();
                    var sMap = supplierMap.Select($"SupplierHotelId='{supplierHId}'");

                    var dic = new Dictionary<Guid, string>();

                    var hasSMap = false;
                    foreach (DataRow dr in sMap)
                    {
                        var hotelId = Guid.Parse(dr["HotelId"].ToString());
                        if (!dic.ContainsKey(hotelId))
                            dic.Add(hotelId, dr["HotelName"].ToString());

                        hasSMap = true;
                    }

                    var hasTmap = false;
                    foreach (DataRow dr in tMap)
                    {
                        var hotelId = Guid.Parse(dr["HotelId"].ToString());
                        if (!dic.ContainsKey(hotelId))
                            dic.Add(hotelId, dr["HotelName"].ToString());

                        hasTmap = true;
                    }

                    if (hasTmap && !hasSMap)
                    {
                        var drN = oneMap.NewRow();
                        drN["HotelId"] = tMap[0]["HotelId"];
                        drN["SupplierHotelId"] = supplierHId;
                        drN["HotelName"] = tMap[0]["HotelName"]; ;
                        drN["SupplierId"] = supplierId;
                        drN["BatId"] = batId;
                        oneMap.Rows.Add(drN);
                    }

                    if (dic.Count > 1)
                    {
                        var groupId = Guid.NewGuid();
                        foreach (var d in dic)
                        {
                            var drN = rstDt.NewRow();
                            drN["HotelId"] = d.Key;
                            drN["HotelName"] = d.Value;
                            drN["GroupId"] = groupId;
                            drN["SupplierId"] = supplierId;
                            drN["BatId"] = batId;
                            rstDt.Rows.Add(drN);
                        }
                    }
                    //else if (dic.Count == 1)
                    //{
                    //    //oneList.Add(dic.Keys.First());
                    //    var drN = rstDt.NewRow();
                    //    drN["HotelId"] = dic.Keys.First();
                    //    drN["HotelName"] = dic.Values.First();
                    //    drN["GroupId"] = Guid.Empty;
                    //    drN["SupplierId"] = supplierId;
                    //    drN["BatId"] = batId;
                    //    rstDt.Rows.Add(drN);
                    //}
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception);
                }
            }

            //保存到临时关联表
            if (rstDt.Rows.Count > 0)
            {
                var s = DbHelperSQL.SaveListDataBat(rstDt, "HotelMergeSupplierGroup");
            }

            if (oneMap.Rows.Count > 0)
            {
                var s = DbHelperSQL.SaveListDataBat(oneMap, "HotelMergeSupplierAdd");
            }

            MessageBox.Show($"保存成功，共{rstDt.Rows.Count}条记录，Add{oneMap.Rows.Count}条");
        }

        private void HotelMergeSupplierDeal(string spplierName)
        {
            try
            {
                var supplierOrderList = new List<(string, string)>()
                {
                    ("607B69C3-2DBA-41B5-BCAF-10A014FAF1DD", "畅联-亚朵"),
                    //("B6C0D180-D96F-4FCB-A8CF-A16DA3DD987F", "畅联-维也纳"),
                    ("073A26B3-874C-4903-B787-6465EE23789B", "如家"),
                    ("0C1C1CDC-C831-4ECF-B362-A47F0E1AC529", "锦江-铂涛直连"),
                    ("D1D0C7F2-DC98-4B64-B36B-F6CD9E7FBB08", "畅联-格林"),
                    ("CE833C6E-D06F-46A3-B53E-204E91E83AB4", "畅联-君亭"),
                    ("557C7AB5-5816-4C1F-8AED-339A96177143", "东呈直连"),
                    ("89E38E71-47D3-4946-92F4-B41E55ECD38F", "华住直连"),
                    ("118F9754-0B86-47CB-AAC2-FC75220D90A6", "畅联-都市118"),

                    ("A7685C06-629D-454B-98DA-81F4E6DE1433", "携程直连"),
                    ("B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC", "美团直连"),
                    ("798BD348-9911-4DB4-92CA-848BB0064EC0", "千淘科技"),
                    ("61FDCC1D-888B-4A53-98ED-40AC18A156CB", "途牛笛风"),
                    ("24924AF6-48D4-4B7A-BA81-103DA6084C69", "四海度假"),
                    ("A8375567-B63C-1B9D-F2BC-AA96D2605F53", "丽呈直连"),
                    ("138E28A8-17AC-47BD-AD2F-4C032CC1F994", "神兽国旅"),
                    ("00000000-0000-0000-0000-000000000000", "自营")
                };

                var strSql = @"SELECT [HotelId]
                            ,[GroupId]
                            , b.SupplierId
                        FROM [HotelMergeSupplierGroup] g
                            left join [dbo].[HotelBasicInfoes] b on g.HotelId = b.id";

                var dt = DbHelperSQL.Query(strSql);

                if (dt?.Tables[0] != null && dt.Tables[0].Rows.Count > 0)
                {
                    //按分组组合出待合并酒店
                    Dictionary<Guid, List<HotelItem>> dic = new Dictionary<Guid, List<HotelItem>>();

                    foreach (DataRow dr in dt.Tables[0].Rows)
                    {
                        var groupId = Guid.Parse(dr["GroupId"].ToString());

                        Guid.TryParse(dr["SupplierId"].ToString(), out var supplierId);

                        if (dic.ContainsKey(groupId))
                        {
                            var list = dic[groupId];
                            list.Add(new HotelItem
                            {
                                HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                SupplierId = supplierId
                            });
                        }
                        else
                        {
                            dic.Add(groupId, new List<HotelItem>()
                            {
                                new HotelItem
                                {
                                    HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                    SupplierId = supplierId
                                }
                            });
                        }
                    }

                    //查询mapping表多供应商映射
                    var mappingDt = DbHelperSQL
                        .Query("select * from HotelMappings where HotelId in(select hotelid from [HotelMergeSupplierGroup])")
                        .Tables[0];

                    if (mappingDt.Rows.Count > 0)
                    {
                        var mappingDic = mappingDt.AsEnumerable().GroupBy(h => Guid.Parse(h["HotelId"].ToString()))
                            .ToDictionary(h => h.Key, h => h.ToList());

                        var mappingSaveDt = mappingDt.Clone(); //整理好的映射，将替换成正式数据
                        var mappingRptDt = mappingDt.Clone(); //重复合并的映射，保存后备查

                        var delHotelDt = new DataTable();
                        delHotelDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));

                        foreach (var item in dic)
                        {
                            var hList = item.Value;
                            var supplierTmp = new List<Guid>();

                            //判断使用哪个酒店留底合并
                            var hotelId = Guid.Empty;
                            foreach (var i in supplierOrderList)
                            {
                                var h = hList.FirstOrDefault(m => m.SupplierId == Guid.Parse(i.Item1));
                                if (h != null)
                                {
                                    hotelId = h.HotelId;
                                    break;
                                }
                            }

                            //整理多供应商Mapping映射记录
                            foreach (var hotelItem in hList)
                            {
                                if (mappingDic.ContainsKey(hotelItem.HotelId))
                                {
                                    var mappingList = mappingDic[hotelItem.HotelId];

                                    if (hotelItem.HotelId == hotelId)
                                    {
                                        mappingList.ForEach(m =>
                                        {
                                            var mpSupplierId = Guid.Parse(m["SupplierId"].ToString());
                                            if (supplierTmp.Contains(mpSupplierId))
                                            {
                                                mappingRptDt.Rows.Add(m.ItemArray);
                                            }
                                            else
                                            {
                                                supplierTmp.Add(mpSupplierId);
                                                mappingSaveDt.Rows.Add(m.ItemArray);
                                            }
                                        });
                                    }
                                    else
                                    {
                                        foreach (var mp in mappingList)
                                        {
                                            var mpSupplierId = Guid.Parse(mp["SupplierId"].ToString());
                                            if (supplierTmp.Contains(mpSupplierId))
                                            {
                                                mappingRptDt.Rows.Add(mp.ItemArray);
                                            }
                                            else
                                            {
                                                mp["HotelId"] = hotelId;
                                                supplierTmp.Add(mpSupplierId);

                                                mappingSaveDt.Rows.Add(mp.ItemArray);
                                            }
                                        }

                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                                else
                                {
                                    if (hotelItem.HotelId != hotelId)
                                    {
                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                            }
                        }

                        //保存处理结果
                        if (mappingSaveDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingSupplierMerge");
                            DbHelperSQL.SaveListDataBat(mappingSaveDt, "HotelMappingSupplierMerge");

                            LogHelper.WriteLog($"{spplierName}-保存处理结果，共{mappingSaveDt.Rows.Count}");
                        }

                        if (mappingRptDt.Rows.Count > 0)
                        {
                            //DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingSupplierMergeRpt");
                            DbHelperSQL.SaveListDataBat(mappingRptDt, "HotelMappingSupplierMergeRpt");
                            LogHelper.WriteLog($"{spplierName}-mappingRptDt，共{mappingRptDt.Rows.Count}");
                        }

                        if (delHotelDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingSupplierMergeDel");
                            DbHelperSQL.SaveListDataBat(delHotelDt, "HotelMappingSupplierMergeDel");
                            LogHelper.WriteLog($"{spplierName}-需逻辑删除合并掉的酒店，共{delHotelDt.Rows.Count}");
                        }

                        //return;

                        var sqlList = new List<string>();
                        //删除待处理的映射数据
                        sqlList.Add("DELETE FROM HotelMappings WHERE HotelId in(SELECT hotelid FROM [HotelMergeSupplierGroup])");

                        //插入整理后替换的映射结果
                        sqlList.Add(@"INSERT INTO [dbo].[HotelMappings]
                                       ([Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,[CreationUserName]
                                       ,[CreationTime]
                                       ,[LastModificationUserId]
                                       ,[LastModificationUserName]
                                       ,[LastModificationTime]
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual])
                                 SELECT
                                       [Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[LastModificationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual]
		                               FROM HotelMappingSupplierMerge");

                        //逻辑删除被合并掉的酒店
                        sqlList.Add("UPDATE [HotelBasicInfoes] SET IsDeleted=1,DeletionTime=GETDATE(),DeleterUserName='SysMerge',LastModificationTime=GETDATE() WHERE ID IN(SELECT HOTELID FROM HotelMappingSupplierMergeDel)");

                        DbHelperSQL.ExecuteSqlTran(sqlList);
                    }
                }

                //MessageBox.Show("完成!");
            }
            catch (Exception e)
            {
                //MessageBox.Show(e.Message);
                LogHelper.logError.Error(e);
            }
        }

        private void HotelSupplierAddMapping()
        {
            try
            {

                var sqlList = new List<string>();

                //插入整理后替换的映射结果
                sqlList.Add(@"INSERT INTO [dbo].[HotelMappings]
                                       ([Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,[CreationUserName]
                                       ,[CreationTime]
                                       ,[LastModificationUserId]
                                       ,[LastModificationUserName]
                                       ,[LastModificationTime]
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual])
                                 SELECT
                                       NewId()
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,1
                                       ,'00000000-0000-0000-0000-000000000000'
                                       ,'SysMergeAdd'
                                       ,GETDATE()
                                       ,null
                                       ,null
                                       ,null
                                       ,0
                                       ,null
                                       ,null
                                       ,[DeletionTime]
                                       ,1
                                       ,1
                                       ,0
		                               FROM [HotelMergeSupplierAdd]");

                DbHelperSQL.ExecuteSqlTran(sqlList);
            }
            catch (Exception e)
            {
                LogHelper.WriteLog(e.Message, e);
                MessageBox.Show(e.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(this.comboBox1.SelectedValue.ToString()))
            {
                MessageBox.Show("请选择直连酒店供应商！");
                return;
            }
            if (string.IsNullOrEmpty(this.comboBox2.SelectedValue.ToString()))
            {
                MessageBox.Show("请选择对应映射供应商！");
                return;
            }

            if (MessageBox.Show("是否确定进行合并", "确认操作", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                HotelMergeSupplierDeal(this.comboBox1.Text);

                HotelSupplierAddMapping();

                MessageBox.Show("合并完成！");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "*.xlsx(Excel文件) | *.xlsx|*.xls(Excel文件) | *.xls|*.*(所有文件)|*.*";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                //MessageBox.Show(openFileDialog.FileName);
                SaveExcelData(openFileDialog.FileName);
            }
        }

        public void SaveExcelData(string strExcelPath)
        {
            var dt = ReadExcelToTable(strExcelPath);

            MessageBox.Show(dt.Rows.Count.ToString());

            if (dt != null && dt.Rows.Count > 0)
            {
                var mpDt = DbHelperSQL.Query("SELECT * FROM HotelMeger_SupplierMapping WHERE 1=0").Tables[0];
                if (mpDt != null)
                {
                    int i = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        i++;
                        if (i == 1)
                            continue;

                        if (string.IsNullOrEmpty(dr[0].ToString()))
                        {
                            continue;
                        }

                        var drN = mpDt.NewRow();
                        drN["ToHotelId"] = dr[0];
                        drN["SupplierHId"] = dr[1];
                        drN["HotelName"] = Convert.ToString(dr[2]) ;
                        mpDt.Rows.Add(drN);
                        
                    }
                }

                DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMeger_SupplierMapping");
                DbHelperSQL.SaveListDataBat(mpDt, "HotelMeger_SupplierMapping");

                this.dataGridView1.DataSource = mpDt;
            }
        }

        private DataTable ReadExcelToTable(string path)
        {
            try
            {
                // 连接字符串(Office 07及以上版本 不能出现多余的空格 而且分号注意)
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
                // 连接字符串(Office 07以下版本, 基本上上面的连接字符串就可以了) 
                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    // 取得所有sheet的名字
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                    // 取得第一个sheet的名字
                    string firstSheetName = sheetsName.Rows[0][2].ToString();

                    // 查询字符串 
                    string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);

                    // OleDbDataAdapter是充当 DataSet 和数据源之间的桥梁，用于检索和保存数据
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);

                    // DataSet是不依赖于数据库的独立数据集合
                    DataSet set = new DataSet();

                    // 使用 Fill 将数据从数据源加载到 DataSet 中
                    ada.Fill(set);

                    return set.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
                return null;
            }
        }

        public DataTable GetExcelTableNames(string strExcelPath)
        {
            try
            {
                DataTable dtExcel = new DataTable();
                //数据表
                DataSet ds = new DataSet();
                //获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(strExcelPath);
                string strFileName = System.IO.Path.GetFileName(strExcelPath);
                //Excel的连接
                OleDbConnection objConn = null;
                switch (strExtension)
                {
                    case ".xls":
                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                        break;
                    case ".xlsx":
                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        break;
                    default:
                        objConn = null;
                        break;
                }
                if (objConn == null)
                {
                    return null;
                }
                objConn.Open();
                //获取Excel中所有Sheet表的信息
                System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                //string lstSheetNames;
                List<string> lstSheetNames = new List<string>();

                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string strSheetName = (string)schemaTable.Rows[i]["TABLE_NAME"];
                    if (strSheetName.Contains("$") && !strSheetName.Replace("'", "").EndsWith("$"))
                    {
                        //过滤无效SheetName完毕....
                        continue;
                    }
                    if (lstSheetNames != null && !lstSheetNames.Contains(strSheetName))
                        strSheetName = strSheetName.Replace("$", "").Replace("'", "");

                    lstSheetNames.Add(strSheetName);
                }
                objConn.Close();
                return dtExcel;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
                return null;
            }
        }
    }

    class SupplierInfo
    {
        public SupplierInfo()
        {

        }

        public SupplierInfo(string supplierId, string supplierName)
        {
            SupplierId = supplierId;
            SupplierName = supplierName;
        }
        public string SupplierId { get; set; }
        public string SupplierName { get; set; }
    }
}
